/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package it.master.cloud.carsharing.webservice;

import it.master.cloud.carsharing.dbmanager.DBManager;
import it.master.cloud.carsharing.domain.RouteJSON;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.ejb.Stateless;
import javax.jws.WebMethod;
import javax.jws.WebParam;
import javax.jws.WebService;

/**
 *
 * @author cloud
 */
@WebService(serviceName = "GetRoutes")
@Stateless()
public class WebServices {

    /**
     * This is a sample web service operation
     */
    @WebMethod(operationName = "hello")
    public String hello(@WebParam(name = "name") String txt) {
        return "Hello " + txt + " !";
    }

    /**
     * Web service operation
     */
    @WebMethod(operationName = "getRoutes")
    public List<RouteJSON> getRoutes(@WebParam(name = "startHour") String startHour, 
            @WebParam(name = "arriveHour") String arriveHour, 
            @WebParam(name = "addressFrom") String addressFrom, 
            @WebParam(name = "addressTo") String addressTo,
            @WebParam(name = "seats") Integer seats) {
        
        try {
            // richiedo apertura di connessione al DB
            Connection c = DBManager.accessDB();
            
            //… codice di interrogazione delle tabelle del DB;
            Statement st = c.createStatement();
            // esecuzione di una query
            // e salvataggio del risultato in oggetto rs
            // NB: rs e’ un oggetto che contiene una tabella le
            // cui tuple costituiscono il risultato della query
            
            String query = "SELECT * FROM USERS U "
                    + " INNER JOIN ROUTES R ON U.USER_ID = R.USER_ID "
                    + " WHERE 1=1 ";
            
            if (!startHour.equals("")) {
                query += " AND R.START_HOUR = '" + startHour + "'";
            }
            if (!arriveHour.equals("")) {
                query += " AND R.ARRIVE_HOUR = '" + arriveHour + "'";
            }
            if (!addressFrom.equals("")) {
                query += " AND R.ADDRESS_FROM = '" + addressFrom + "'";
            }
            if (!addressTo.equals("")) {
                query += " AND R.ADDRESS_TO = '" + addressTo + "'";
            }
            if (seats != null) {
                query += " AND R.SEATS >= " + seats;
            }
            
            System.out.println("Query = " + query);
            ResultSet rs = st.executeQuery(query);
            
            final List<RouteJSON> list = new ArrayList<>();
            
            //… codice di analisi del risultato della query
            while (rs.next()) {
                
                RouteJSON routejson = new RouteJSON();
                
                routejson.setName(rs.getString("NAME"));
                routejson.setSurname(rs.getString("SURNAME"));
                routejson.setMail(rs.getString("MAIL"));
                routejson.setPhone(rs.getString("PHONE"));
                routejson.setAddressFrom(rs.getString("ADDRESS_FROM"));
                routejson.setAddressTo(rs.getString("ADDRESS_TO"));
                routejson.setStartHour(rs.getString("START_HOUR"));
                routejson.setArriveHour(rs.getString("ARRIVE_HOUR"));
                routejson.setSeats(rs.getString("SEATS"));
                
                list.add(routejson);
                                
            }
            rs.close();		// chiusura oggetto ResultSet (non serve piu’)
            st.close();		// chiusura statement (non serve piu’) 

            c.close();	// chiusura connessionereturn null;
            
            return list;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        
        return null;
            
    }


    /**
     * Web service operation
     */
    @WebMethod(operationName = "insertRoute")
    public String insertRoute(@WebParam(name = "user_id") Integer user_id, 
            @WebParam(name = "address_from") String address_from, 
            @WebParam(name = "address_to") String address_to, 
            @WebParam(name = "start_hour") String start_hour,
            @WebParam(name = "arrive_hour") String arrive_hour, 
            @WebParam(name = "seats") Integer seats) {
        try {
            // richiedo apertura di connessione al DB
            Connection c = DBManager.accessDB();
            
            //… codice di interrogazione delle tabelle del DB;
            Statement st = c.createStatement();
            
            // esecuzione di una query di update
            // e salvataggio del risultato in oggetto rs
            // NB: rs e’ un oggetto che contiene una tabella le
            // cui tuple costituiscono il risultato della query
            ResultSet rs  = st.executeQuery("SELECT MAX(ROUTE_ID) AS ROUTE_ID FROM ROUTES");
            
            Integer route_id = 1;
             //… codice di analisi del risultato della query
            while (rs.next()) {

                if ( rs.getString("ROUTE_ID") != null ) {
                    route_id = Integer.valueOf(rs.getString("ROUTE_ID")) + 1;                    
                }

                               
            }
                            
            st.close();		// chiusura statement (non serve piu’)
            
            //… codice di interrogazione delle tabelle del DB;
            Statement st2 = c.createStatement();
            
            st2.executeUpdate("INSERT INTO ROUTES (USER_ID, ADDRESS_FROM, ADDRESS_TO,"
                    + "START_HOUR, ARRIVE_HOUR, SEATS) VALUES ("
                    + user_id + ", "
                    + "'" + address_from + "', "
                    + "'" + address_to + "', "
                    + "'" + start_hour + "', "
                    + "'" + arrive_hour + "', "
                    + seats + ")");
            
            st2.close();		// chiusura statement (non serve piu’) 
            c.close();	// chiusura connessione
            
            return "success";
            
        } catch (SQLException e) {
            return "error" + (e.getMessage());
        }
        
    }
    
    


    /**
     * Web service operation
     */
    @WebMethod(operationName = "register")
    public String register(@WebParam(name = "name") String name, 
            @WebParam(name = "surname") String surname, 
            @WebParam(name = "login") String login, 
            @WebParam(name = "password") String password, 
            @WebParam(name = "mail") String mail, 
            @WebParam(name = "phone") String phone) {
    
         try {
            // richiedo apertura di connessione al DB
            Connection c = DBManager.accessDB();
            
            //… codice di interrogazione delle tabelle del DB;
            Statement st = c.createStatement();
            
            // esecuzione di una query di inserimento
            st.executeUpdate("INSERT INTO USERS"
                    + "(NAME, SURNAME, LOGIN, PASSWORD, MAIL, PHONE)"
                    + "VALUES ('" + name + "'"
                    + ", '" + surname + "'"
                    + ", '" + login  + "'"
                    + ", '" + password  + "'"
                    + ", '" + mail  + "'"
                    + ", '" + phone  + "'" + ")");
                            
            st.close();  // chiusura statement (non serve piu’) 

            c.close();	// chiusura connessione
            
            return "success";
        } catch (SQLException e) {
            System.out.print(e.getMessage());
            return "error: " + e.getMessage();
        }
    }
    

    
}